<!DOCTYPE html><html lang="zh-CN" data-theme="light"><head><meta charset="UTF-8"><meta http-equiv="X-UA-Compatible" content="IE=edge"><meta name="viewport" content="width=device-width, initial-scale=1.0, maximum-scale=1.0, user-scalable=no"><title>数据库 | 星Z痕</title><meta name="keywords" content="笔记,数据库"><meta name="author" content="星Z痕"><meta name="copyright" content="星Z痕"><meta name="format-detection" content="telephone=no"><meta name="theme-color" content="#ffffff"><meta name="description" content="第一章数据库 DB      特点:冗余度较小           数据独立性高           数据完整性           数据共享 数据库管理系统 DBMS         介于应用程序和操作系统之间         (位于用户和数据库之间) 数据库系统 DBS 数据库系统三级结构模式外模式(用户级)       也称子模式或用户模式       一个数据库可以有多个外模式 模式(概念">
<meta property="og:type" content="article">
<meta property="og:title" content="数据库">
<meta property="og:url" content="https://liy002.gitee.io/2022/01/18/sql/index.html">
<meta property="og:site_name" content="星Z痕">
<meta property="og:description" content="第一章数据库 DB      特点:冗余度较小           数据独立性高           数据完整性           数据共享 数据库管理系统 DBMS         介于应用程序和操作系统之间         (位于用户和数据库之间) 数据库系统 DBS 数据库系统三级结构模式外模式(用户级)       也称子模式或用户模式       一个数据库可以有多个外模式 模式(概念">
<meta property="og:locale" content="zh_CN">
<meta property="og:image" content="https://www.helloimg.com/images/2022/09/24/Z89nc6.png">
<meta property="article:published_time" content="2022-01-18T10:31:00.000Z">
<meta property="article:modified_time" content="2022-04-11T11:28:00.499Z">
<meta property="article:author" content="星Z痕">
<meta property="article:tag" content="笔记">
<meta property="article:tag" content="数据库">
<meta name="twitter:card" content="summary">
<meta name="twitter:image" content="https://www.helloimg.com/images/2022/09/24/Z89nc6.png"><link rel="shortcut icon" href="/img/favicon.png"><link rel="canonical" href="https://liy002.gitee.io/2022/01/18/sql/"><link rel="preconnect" href="//cdn.jsdelivr.net"/><link rel="preconnect" href="//busuanzi.ibruce.info"/><link rel="stylesheet" href="/css/index.css"><link rel="stylesheet" href="https://cdn.jsdelivr.net/npm/@fortawesome/fontawesome-free@6/css/all.min.css" media="print" onload="this.media='all'"><link rel="stylesheet" href="https://cdn.jsdelivr.net/npm/@fancyapps/ui/dist/fancybox.css" media="print" onload="this.media='all'"><script>const GLOBAL_CONFIG = { 
  root: '/',
  algolia: undefined,
  localSearch: {"path":"search.xml","languages":{"hits_empty":"找不到您查询的内容：${query}"}},
  translate: undefined,
  noticeOutdate: undefined,
  highlight: {"plugin":"prismjs","highlightCopy":true,"highlightLang":true,"highlightHeightLimit":false},
  copy: {
    success: '复制成功',
    error: '复制错误',
    noSupport: '浏览器不支持'
  },
  relativeDate: {
    homepage: false,
    post: false
  },
  runtime: '天',
  date_suffix: {
    just: '刚刚',
    min: '分钟前',
    hour: '小时前',
    day: '天前',
    month: '个月前'
  },
  copyright: undefined,
  lightbox: 'fancybox',
  Snackbar: undefined,
  source: {
    justifiedGallery: {
      js: 'https://cdn.jsdelivr.net/npm/flickr-justified-gallery@2/dist/fjGallery.min.js',
      css: 'https://cdn.jsdelivr.net/npm/flickr-justified-gallery@2/dist/fjGallery.min.css'
    }
  },
  isPhotoFigcaption: false,
  islazyload: false,
  isAnchor: false
}</script><script id="config-diff">var GLOBAL_CONFIG_SITE = {
  title: '数据库',
  isPost: true,
  isHome: false,
  isHighlightShrink: false,
  isToc: true,
  postUpdate: '2022-04-11 19:28:00'
}</script><noscript><style type="text/css">
  #nav {
    opacity: 1
  }
  .justified-gallery img {
    opacity: 1
  }

  #recent-posts time,
  #post-meta time {
    display: inline !important
  }
</style></noscript><script>(win=>{
    win.saveToLocal = {
      set: function setWithExpiry(key, value, ttl) {
        if (ttl === 0) return
        const now = new Date()
        const expiryDay = ttl * 86400000
        const item = {
          value: value,
          expiry: now.getTime() + expiryDay,
        }
        localStorage.setItem(key, JSON.stringify(item))
      },

      get: function getWithExpiry(key) {
        const itemStr = localStorage.getItem(key)

        if (!itemStr) {
          return undefined
        }
        const item = JSON.parse(itemStr)
        const now = new Date()

        if (now.getTime() > item.expiry) {
          localStorage.removeItem(key)
          return undefined
        }
        return item.value
      }
    }
  
    win.getScript = url => new Promise((resolve, reject) => {
      const script = document.createElement('script')
      script.src = url
      script.async = true
      script.onerror = reject
      script.onload = script.onreadystatechange = function() {
        const loadState = this.readyState
        if (loadState && loadState !== 'loaded' && loadState !== 'complete') return
        script.onload = script.onreadystatechange = null
        resolve()
      }
      document.head.appendChild(script)
    })
  
      win.activateDarkMode = function () {
        document.documentElement.setAttribute('data-theme', 'dark')
        if (document.querySelector('meta[name="theme-color"]') !== null) {
          document.querySelector('meta[name="theme-color"]').setAttribute('content', '#0d0d0d')
        }
      }
      win.activateLightMode = function () {
        document.documentElement.setAttribute('data-theme', 'light')
        if (document.querySelector('meta[name="theme-color"]') !== null) {
          document.querySelector('meta[name="theme-color"]').setAttribute('content', '#ffffff')
        }
      }
      const t = saveToLocal.get('theme')
    
          if (t === 'dark') activateDarkMode()
          else if (t === 'light') activateLightMode()
        
      const asideStatus = saveToLocal.get('aside-status')
      if (asideStatus !== undefined) {
        if (asideStatus === 'hide') {
          document.documentElement.classList.add('hide-aside')
        } else {
          document.documentElement.classList.remove('hide-aside')
        }
      }
    
    const detectApple = () => {
      if(/iPad|iPhone|iPod|Macintosh/.test(navigator.userAgent)){
        document.documentElement.classList.add('apple')
      }
    }
    detectApple()
    })(window)</script><link rel="stylesheet" href="/touming/tm.css"><script src="https://fastly.jsdelivr.net/gh/Weidows-projects/live2d-moc3/dist/live2dcubismcore.min.js"></script><script src="https://fastly.jsdelivr.net/gh/Weidows-projects/live2d-moc3/dist/pixi.min.js"></script><script src="https://fastly.jsdelivr.net/gh/Weidows-projects/live2d-moc3/dist/live2d.min.js"></script><meta name="generator" content="Hexo 6.3.0"><link rel="alternate" href="/atom.xml" title="星Z痕" type="application/atom+xml">
</head><body><div id="loading-box"><div class="loading-left-bg"></div><div class="loading-right-bg"></div><div class="spinner-box"><div class="configure-border-1"><div class="configure-core"></div></div><div class="configure-border-2"><div class="configure-core"></div></div><div class="loading-word">加载中...</div></div></div><div id="web_bg"></div><div id="sidebar"><div id="menu-mask"></div><div id="sidebar-menus"><div class="avatar-img is-center"><img src="/img/%E5%A4%B4%E5%83%8F.jpg" onerror="onerror=null;src='/img/friend_404.gif'" alt="avatar"/></div><div class="sidebar-site-data site-data is-center"><a href="/archives/"><div class="headline">文章</div><div class="length-num">20</div></a><a href="/tags/"><div class="headline">标签</div><div class="length-num">12</div></a><a href="/categories/"><div class="headline">分类</div><div class="length-num">1</div></a></div><hr/><div class="menus_items"><div class="menus_item"><a class="site-page" href="/"><i class="fa-fw fas fa-home"></i><span> 首页</span></a></div><div class="menus_item"><a class="site-page group" href="javascript:void(0);"><i class="fa-fw fas fa-list"></i><span> 列表</span><i class="fas fa-chevron-down"></i></a><ul class="menus_item_child"><li><a class="site-page child" href="/archives/"><i class="fa-fw fas fa-archive"></i><span> 归档</span></a></li><li><a class="site-page child" href="/tags/"><i class="fa-fw fas fa-tags"></i><span> 标签</span></a></li><li><a class="site-page child" href="/categories/"><i class="fa-fw fas fa-folder-open"></i><span> 分类</span></a></li></ul></div><div class="menus_item"><a class="site-page group" href="javascript:void(0);"><i class="fa-fw fas fa-solid fa-shuttle-space"></i><span> 传送门</span><i class="fas fa-chevron-down"></i></a><ul class="menus_item_child"><li><a class="site-page child" target="_blank" rel="noopener" href="https://liy001.github.io/"><span> GitHub站</span></a></li><li><a class="site-page child" href="https://liy002.gitee.io"><span> Gitee站</span></a></li></ul></div><div class="menus_item"><a class="site-page" href="/link/"><i class="fa-fw fas fa-link"></i><span> 友链</span></a></div><div class="menus_item"><a class="site-page" href="/about/"><i class="fa-fw fas fa-heart"></i><span> 关于</span></a></div><div class="menus_item"><a class="site-page" target="_blank" rel="noopener" href="https://travellings.link"><i class="fa-fw fas fa-subway"></i><span> 开往</span></a></div></div></div></div><div class="post" id="body-wrap"><header class="post-bg" id="page-header" style="background-image: url('https://www.helloimg.com/images/2022/09/24/Z89nc6.png')"><nav id="nav"><span id="blog_name"><a id="site-name" href="/">星Z痕</a></span><div id="menus"><div id="search-button"><a class="site-page social-icon search"><i class="fas fa-search fa-fw"></i><span> 搜索</span></a></div><div class="menus_items"><div class="menus_item"><a class="site-page" href="/"><i class="fa-fw fas fa-home"></i><span> 首页</span></a></div><div class="menus_item"><a class="site-page group" href="javascript:void(0);"><i class="fa-fw fas fa-list"></i><span> 列表</span><i class="fas fa-chevron-down"></i></a><ul class="menus_item_child"><li><a class="site-page child" href="/archives/"><i class="fa-fw fas fa-archive"></i><span> 归档</span></a></li><li><a class="site-page child" href="/tags/"><i class="fa-fw fas fa-tags"></i><span> 标签</span></a></li><li><a class="site-page child" href="/categories/"><i class="fa-fw fas fa-folder-open"></i><span> 分类</span></a></li></ul></div><div class="menus_item"><a class="site-page group" href="javascript:void(0);"><i class="fa-fw fas fa-solid fa-shuttle-space"></i><span> 传送门</span><i class="fas fa-chevron-down"></i></a><ul class="menus_item_child"><li><a class="site-page child" target="_blank" rel="noopener" href="https://liy001.github.io/"><span> GitHub站</span></a></li><li><a class="site-page child" href="https://liy002.gitee.io"><span> Gitee站</span></a></li></ul></div><div class="menus_item"><a class="site-page" href="/link/"><i class="fa-fw fas fa-link"></i><span> 友链</span></a></div><div class="menus_item"><a class="site-page" href="/about/"><i class="fa-fw fas fa-heart"></i><span> 关于</span></a></div><div class="menus_item"><a class="site-page" target="_blank" rel="noopener" href="https://travellings.link"><i class="fa-fw fas fa-subway"></i><span> 开往</span></a></div></div><div id="toggle-menu"><a class="site-page"><i class="fas fa-bars fa-fw"></i></a></div></div></nav><div id="post-info"><h1 class="post-title">数据库</h1><div id="post-meta"><div class="meta-firstline"><span class="post-meta-date"><i class="far fa-calendar-alt fa-fw post-meta-icon"></i><span class="post-meta-label">发表于</span><time class="post-meta-date-created" datetime="2022-01-18T10:31:00.000Z" title="发表于 2022-01-18 18:31:00">2022-01-18</time><span class="post-meta-separator">|</span><i class="fas fa-history fa-fw post-meta-icon"></i><span class="post-meta-label">更新于</span><time class="post-meta-date-updated" datetime="2022-04-11T11:28:00.499Z" title="更新于 2022-04-11 19:28:00">2022-04-11</time></span><span class="post-meta-categories"><span class="post-meta-separator">|</span><i class="fas fa-inbox fa-fw post-meta-icon"></i><a class="post-meta-categories" href="/categories/%E7%AC%94%E8%AE%B0/">笔记</a></span></div><div class="meta-secondline"><span class="post-meta-separator">|</span><span class="post-meta-wordcount"><i class="far fa-file-word fa-fw post-meta-icon"></i><span class="post-meta-label">字数总计:</span><span class="word-count">2.5k</span><span class="post-meta-separator">|</span><i class="far fa-clock fa-fw post-meta-icon"></i><span class="post-meta-label">阅读时长:</span><span>8分钟</span></span><span class="post-meta-separator">|</span><span class="post-meta-pv-cv" id="" data-flag-title="数据库"><i class="far fa-eye fa-fw post-meta-icon"></i><span class="post-meta-label">阅读量:</span><span id="busuanzi_value_page_pv"></span></span></div></div></div></header><main class="layout" id="content-inner"><div id="post"><article class="post-content" id="article-container"><h1 id="第一章"><a href="#第一章" class="headerlink" title="第一章"></a>第一章</h1><pre class="line-numbers language-none"><code class="language-none">数据库 DB
     特点:冗余度较小
          数据独立性高
          数据完整性
          数据共享
数据库管理系统 DBMS
        介于应用程序和操作系统之间
        (位于用户和数据库之间)
数据库系统 DBS<span aria-hidden="true" class="line-numbers-rows"><span></span><span></span><span></span><span></span><span></span><span></span><span></span><span></span><span></span></span></code></pre>
<h2 id="数据库系统三级结构模式"><a href="#数据库系统三级结构模式" class="headerlink" title="数据库系统三级结构模式"></a>数据库系统三级结构模式</h2><pre class="line-numbers language-none"><code class="language-none">外模式(用户级)
      也称子模式或用户模式
      一个数据库可以有多个外模式
模式(概念级)
     也称逻辑模式或概念模式
内模式(物理级)
       也称存储模式<span aria-hidden="true" class="line-numbers-rows"><span></span><span></span><span></span><span></span><span></span><span></span><span></span></span></code></pre>
<hr>
<h1 id="第二章-关系数据库"><a href="#第二章-关系数据库" class="headerlink" title="第二章 关系数据库"></a>第二章 关系数据库</h1><h3 id="定义"><a href="#定义" class="headerlink" title="定义"></a>定义</h3><pre class="line-numbers language-none"><code class="language-none">域:一组具有相同数据类型的值的集合
笛卡儿积:域上的一种集合运算<span aria-hidden="true" class="line-numbers-rows"><span></span><span></span></span></code></pre>
<h3 id="关系代数"><a href="#关系代数" class="headerlink" title="关系代数"></a>关系代数</h3><pre class="line-numbers language-none"><code class="language-none">并:R∪S
差:R-S
交:R∩S
笛卡儿积:R×S
选择
投影
连接:也称为θ连接
自然连接:是一种特殊的等值连接,用 R ⋈ S 来表示
        先等值连接,再去掉重复的列
当两个关系没有公共属性时,则自然连接为笛卡儿积<span aria-hidden="true" class="line-numbers-rows"><span></span><span></span><span></span><span></span><span></span><span></span><span></span><span></span><span></span><span></span></span></code></pre>
<blockquote>
<p>除 : R÷S 。R中关系包含S中的所有关系,则列出R中除去S表中其余的几列</p>
</blockquote>
<img src="https://www.helloimg.com/images/2022/04/11/RtHGao.jpg">

<hr>
<h4 id="外连接"><a href="#外连接" class="headerlink" title="外连接"></a>外连接</h4><pre class="line-numbers language-none"><code class="language-none">左外连接（d）:取出左侧关系中所有与右侧关系的任一元组都不匹配的元组,用空值填充所有来自右侧关系的属性
右外连接（e）
全外连接（f）:左外连接和右外连接和并<span aria-hidden="true" class="line-numbers-rows"><span></span><span></span><span></span></span></code></pre>
<img src="https://www.helloimg.com/images/2022/04/11/RtHP4Q.jpg">

<hr>
<h1 id="第三章"><a href="#第三章" class="headerlink" title="第三章"></a>第三章</h1><p>基本表的定义</p>
<pre class="line-numbers language-sql" data-language="sql"><code class="language-sql"><span class="token keyword">CREATE</span> <span class="token keyword">TABLE</span> 表名
<span class="token punctuation">(</span> 列名 数据类型<span class="token punctuation">,</span>
  列名 数据类型<span class="token punctuation">,</span>
<span class="token punctuation">)</span><span class="token punctuation">;</span><span aria-hidden="true" class="line-numbers-rows"><span></span><span></span><span></span><span></span></span></code></pre>
<p>基本表的修改</p>
<pre class="line-numbers language-sql" data-language="sql"><code class="language-sql"><span class="token keyword">ALTER</span> <span class="token keyword">TABLE</span> 表名
<span class="token keyword">ADD</span> 新列名 数据类型<span class="token punctuation">;</span>
<span class="token keyword">DROP</span> <span class="token keyword">COLUMN</span> 列名<span class="token punctuation">;</span>
<span class="token keyword">ALTER</span> <span class="token keyword">COLUMN</span> 列名 数据类型<span class="token punctuation">;</span><span aria-hidden="true" class="line-numbers-rows"><span></span><span></span><span></span><span></span></span></code></pre>
<blockquote>
<p>ADD 增加  、DROP 删除  、ALTER 修改</p>
</blockquote>
<hr>
<p>基本表的删除</p>
<pre class="line-numbers language-SQL" data-language="SQL"><code class="language-SQL">DROP TABLE 表名;<span aria-hidden="true" class="line-numbers-rows"><span></span></span></code></pre>
<blockquote>
<p>要删除的基本表不能被其他表的约束所引用</p>
</blockquote>
<hr>
<h2 id="3-3-查询"><a href="#3-3-查询" class="headerlink" title="3.3 查询"></a>3.3 查询</h2><pre class="line-numbers language-sql" data-language="sql"><code class="language-sql"><span class="token keyword">SELECT</span> 目标列表达式     <span class="token comment">---需要那些列</span>
<span class="token keyword">FROM</span> 表名或视图名       <span class="token comment">---来自那些表</span>
<span class="token punctuation">[</span><span class="token keyword">WHERE</span><span class="token operator">&lt;</span>条件表达式<span class="token operator">></span><span class="token punctuation">]</span><span class="token punctuation">;</span>    <span class="token comment">---条件</span><span aria-hidden="true" class="line-numbers-rows"><span></span><span></span><span></span></span></code></pre>
<pre class="line-numbers language-none"><code class="language-none">&lt;目标列表达式&gt;中各个列的先后顺序可以和表中的顺序不一致
查询全部列:在目标列表达式中写星号“ * ”
&lt;目标列表达式&gt;可以是表中存在的属性列,也可以是表达式、字符串常量或函数
函数 datediff(datepart,date1,date2)   返回date1和date2的时间间隔,其单位由datepart决定<span aria-hidden="true" class="line-numbers-rows"><span></span><span></span><span></span><span></span></span></code></pre>
<blockquote>
<p>例3.11 查询所有抢修工程的抢修天数</p>
</blockquote>
<pre class="line-numbers language-SQL" data-language="SQL"><code class="language-SQL">SELECT prj_name, start_date, end_date, datediff(day,start_date,end_date)
FROM Salvaging;<span aria-hidden="true" class="line-numbers-rows"><span></span><span></span></span></code></pre>
<h4 id="去掉表中的重复行"><a href="#去掉表中的重复行" class="headerlink" title="去掉表中的重复行"></a>去掉表中的重复行</h4><pre class="line-numbers language-sql" data-language="sql"><code class="language-sql"><span class="token keyword">SELECT</span> <span class="token keyword">DISTINCT</span> 名称
<span class="token keyword">FROM</span> 名称<span class="token punctuation">;</span><span aria-hidden="true" class="line-numbers-rows"><span></span><span></span></span></code></pre>
<h4 id="确定范围的查询"><a href="#确定范围的查询" class="headerlink" title="确定范围的查询"></a>确定范围的查询</h4><pre class="line-numbers language-sql" data-language="sql"><code class="language-sql"><span class="token operator">BETWEEN</span><span class="token punctuation">.</span><span class="token punctuation">.</span><span class="token punctuation">.</span><span class="token operator">AND</span>
<span class="token operator">NOT</span> <span class="token operator">BETWEEN</span><span class="token punctuation">.</span><span class="token punctuation">.</span><span class="token punctuation">.</span><span class="token operator">AND</span><span aria-hidden="true" class="line-numbers-rows"><span></span><span></span></span></code></pre>
<blockquote>
<p>例3.16  查询单价在50~100的物资名称、数量及其单价</p>
</blockquote>
<pre class="line-numbers language-sql" data-language="sql"><code class="language-sql"><span class="token keyword">SELECT</span> mat_name<span class="token punctuation">,</span>amount<span class="token punctuation">,</span>unit
<span class="token keyword">FROM</span> Stock
<span class="token keyword">WHERE</span> unit <span class="token operator">BETWEEN</span> <span class="token number">50</span> <span class="token operator">AND</span> <span class="token number">100</span><span class="token punctuation">;</span><span aria-hidden="true" class="line-numbers-rows"><span></span><span></span><span></span></span></code></pre>
<h4 id="字符匹配的查询"><a href="#字符匹配的查询" class="headerlink" title="字符匹配的查询"></a>字符匹配的查询</h4><pre class="line-numbers language-sql" data-language="sql"><code class="language-sql"><span class="token punctuation">[</span><span class="token operator">NOT</span><span class="token punctuation">]</span> LINK <span class="token string">'匹配符'</span>
<span class="token comment">--- % (百分号)代表任意长度的字符串。例 a%b:表示以a开头,以b结尾的任意长度的字符串</span>
<span class="token comment">--- _ (下横线)代表任意单个字符。例 a_b:表示以a开头,以b结尾的长度为3的任意字符串</span><span aria-hidden="true" class="line-numbers-rows"><span></span><span></span><span></span></span></code></pre>
<hr>
<h4 id="3-对查询结果进行排序"><a href="#3-对查询结果进行排序" class="headerlink" title="3. 对查询结果进行排序"></a>3. 对查询结果进行排序</h4><pre class="line-numbers language-sql" data-language="sql"><code class="language-sql"><span class="token keyword">ORDER</span> <span class="token keyword">BY</span> 列名<span aria-hidden="true" class="line-numbers-rows"><span></span></span></code></pre>
<h4 id="4-TOP-子句的用法"><a href="#4-TOP-子句的用法" class="headerlink" title="4. TOP 子句的用法"></a>4. TOP 子句的用法</h4><pre class="line-numbers language-none"><code class="language-none">放在 SELECT 后
TOP n 子句:在查询结果中输出前面的n条记录
TOP n PERCENT 子句:在查询结果中输出前面占结果记录总数的n% 条记录<span aria-hidden="true" class="line-numbers-rows"><span></span><span></span><span></span></span></code></pre>
<h4 id="5-聚集函数"><a href="#5-聚集函数" class="headerlink" title="5. 聚集函数"></a>5. 聚集函数</h4><pre class="line-numbers language-none"><code class="language-none">放在 SELECT 后
COUNT (DISTINCT|ALL * )     统计表中元组个数
COUNT 列名                  统计一列中值的个数
SUM 列名                    求和
AVG 列名                    求平均值
MAX 列名                    求最大值
MIN 列名                    求最小值
DISTINCT 短语表示在计算时要取消指定列中的重复值<span aria-hidden="true" class="line-numbers-rows"><span></span><span></span><span></span><span></span><span></span><span></span><span></span><span></span></span></code></pre>
<h3 id="3-3-2-连接查询"><a href="#3-3-2-连接查询" class="headerlink" title="3.3.2 连接查询"></a>3.3.2 连接查询</h3><h4 id="等值与非等值连接查询"><a href="#等值与非等值连接查询" class="headerlink" title="等值与非等值连接查询"></a>等值与非等值连接查询</h4><pre class="line-numbers language-none"><code class="language-none">表名1.列名1 比较运算符 表名2.列名2;
或 表名1.列名1 BETWEEN 表名2.列名2 AND 表名2.列名3;
比较运算符 ＝ 、＞ 、＜ 、＞＝ 、＜＝ 、！＝(或＜＞)
＝ 是 等值连接,其他为非等值连接
在等值连接中把目标列中重复的属性去掉,则为自然连接<span aria-hidden="true" class="line-numbers-rows"><span></span><span></span><span></span><span></span><span></span></span></code></pre>
<h4 id="外连接查询"><a href="#外连接查询" class="headerlink" title="外连接查询"></a>外连接查询</h4><pre class="line-numbers language-none"><code class="language-none">左外连接:LEFT OUTER JOIN
右外连接:RIGHT OUTER JOIN
全外连接:FULL OUTER JOIN<span aria-hidden="true" class="line-numbers-rows"><span></span><span></span><span></span></span></code></pre>
<h4 id="复合条件连接查询"><a href="#复合条件连接查询" class="headerlink" title="复合条件连接查询"></a>复合条件连接查询</h4><pre class="line-numbers language-none"><code class="language-none">WHERE 子句中可以有多个连接条件,称为复合条件查询<span aria-hidden="true" class="line-numbers-rows"><span></span></span></code></pre>
<blockquote>
<p>例3.43 查询项目号为“20100015”的抢修项目所使用的物资编号、物资名称、规格和使用数量</p>
</blockquote>
<pre class="line-numbers language-sql" data-language="sql"><code class="language-sql"><span class="token keyword">SELECT</span> Out_stock<span class="token punctuation">.</span>mat_num<span class="token punctuation">,</span>mat_name<span class="token punctuation">,</span>speci<span class="token punctuation">,</span>Out_stock<span class="token punctuation">.</span>amount
<span class="token keyword">FROM</span> Stock<span class="token punctuation">,</span>Out_stock
<span class="token keyword">WHERE</span> Stock<span class="token punctuation">.</span>mat_num <span class="token operator">=</span> Out_stock<span class="token punctuation">.</span>mat_num <span class="token operator">AND</span> prj_num <span class="token operator">=</span> <span class="token string">'20100015'</span><span aria-hidden="true" class="line-numbers-rows"><span></span><span></span><span></span></span></code></pre>
<h4 id="自身连接查询"><a href="#自身连接查询" class="headerlink" title="自身连接查询"></a>自身连接查询</h4><pre class="line-numbers language-none"><code class="language-none">同一个表与自己进行连接
必须给表取别名加以区分,必须加前缀<span aria-hidden="true" class="line-numbers-rows"><span></span><span></span></span></code></pre>
<blockquote>
<p>例3.45 查询同时使用了物资编号为m001和m002的抢修工程的工程号</p>
</blockquote>
<pre class="line-numbers language-sql" data-language="sql"><code class="language-sql"><span class="token keyword">SELECT</span> A<span class="token punctuation">.</span>prj_num
<span class="token keyword">FROM</span> Out_stock A<span class="token punctuation">,</span> Out_stock B
<span class="token keyword">WHERE</span> A<span class="token punctuation">.</span>prj_num <span class="token operator">=</span> B<span class="token punctuation">.</span>prj_num <span class="token operator">AND</span> A<span class="token punctuation">.</span>mat_num <span class="token operator">=</span> <span class="token string">'m001'</span> <span class="token operator">AND</span> B<span class="token punctuation">.</span>mat_num <span class="token operator">=</span> <span class="token string">'m002'</span><span class="token punctuation">;</span><span aria-hidden="true" class="line-numbers-rows"><span></span><span></span><span></span></span></code></pre>
<h3 id="3-3-3-嵌套查询"><a href="#3-3-3-嵌套查询" class="headerlink" title="3.3.3 嵌套查询"></a>3.3.3 嵌套查询</h3><pre class="line-numbers language-none"><code class="language-none">子查询的 SELECT 语句不能使用 ORDER BY 子句,ORDER BY 子句只能对最终查询结果排序<span aria-hidden="true" class="line-numbers-rows"><span></span></span></code></pre>
<h4 id="带谓词-IN-的嵌套查询"><a href="#带谓词-IN-的嵌套查询" class="headerlink" title="带谓词 IN 的嵌套查询"></a>带谓词 IN 的嵌套查询</h4><blockquote>
<p>例3.47 查询工程项目“观澜站光缆抢修”所使用的物资编号和物资名称</p>
</blockquote>
<pre class="line-numbers language-sql" data-language="sql"><code class="language-sql"><span class="token keyword">SELECT</span> mat_num<span class="token punctuation">,</span>mat_name
<span class="token keyword">FROM</span> Stock
<span class="token keyword">WHERE</span> mat_num <span class="token operator">IN</span>
      <span class="token punctuation">(</span><span class="token keyword">SELECT</span> mat_num
       <span class="token keyword">FROM</span> Out_stock
       <span class="token keyword">WHERE</span> prj_num <span class="token operator">IN</span>
          <span class="token punctuation">(</span><span class="token keyword">SELECT</span> prj_num
           <span class="token keyword">FROM</span> Salvaging
           <span class="token keyword">WHERE</span> prj_name <span class="token operator">=</span> <span class="token string">'观澜站光缆抢修'</span><span class="token punctuation">)</span><span class="token punctuation">)</span><span class="token punctuation">;</span><span aria-hidden="true" class="line-numbers-rows"><span></span><span></span><span></span><span></span><span></span><span></span><span></span><span></span><span></span></span></code></pre>
<h4 id="带有-ANY-或-ALL-谓词的子查询"><a href="#带有-ANY-或-ALL-谓词的子查询" class="headerlink" title="带有 ANY 或 ALL 谓词的子查询"></a>带有 ANY 或 ALL 谓词的子查询</h4><pre class="line-numbers language-none"><code class="language-none">&gt; ANY  大于子查询结果中的某个值
&gt; ALL  大于子查询结果中的所有值...<span aria-hidden="true" class="line-numbers-rows"><span></span><span></span></span></code></pre>
<blockquote>
<p>例3.49 查询其他仓库中比供电局 1# 仓库的某一物资库存量少的物资名称、规格和数量</p>
</blockquote>
<pre class="line-numbers language-sql" data-language="sql"><code class="language-sql"><span class="token keyword">SELECT</span> mat_name<span class="token punctuation">,</span>speci<span class="token punctuation">,</span>amount
<span class="token keyword">FROM</span> Stock
<span class="token keyword">WHERE</span> warehouse <span class="token operator">&lt;></span> <span class="token string">'供电局 1# 仓库'</span>
        <span class="token operator">AND</span> amount <span class="token operator">&lt;</span> <span class="token keyword">ANY</span>
          <span class="token punctuation">(</span><span class="token keyword">SELECT</span> amount
           <span class="token keyword">FROM</span> Stock
           <span class="token keyword">WHERE</span> warehouse <span class="token operator">=</span> <span class="token string">'供电局 1# 仓库'</span><span class="token punctuation">)</span><span class="token punctuation">;</span><span aria-hidden="true" class="line-numbers-rows"><span></span><span></span><span></span><span></span><span></span><span></span><span></span></span></code></pre>
<h3 id="3-3-4-集合查询"><a href="#3-3-4-集合查询" class="headerlink" title="3.3.4. 集合查询"></a>3.3.4. 集合查询</h3><pre class="line-numbers language-none"><code class="language-none">并操作 UNION
交操作 INTERSECT
差操作 EXCEPT
集合操作会自动去除重复元组,保留时要用 ALL 关键词<span aria-hidden="true" class="line-numbers-rows"><span></span><span></span><span></span><span></span></span></code></pre>
<blockquote>
<p>查询存放在供电局 1# 仓库的物资及单价不大于50的物资</p>
</blockquote>
<pre class="line-numbers language-sql" data-language="sql"><code class="language-sql"><span class="token keyword">SELECT</span> <span class="token operator">*</span>
<span class="token keyword">FROM</span> Stock
<span class="token keyword">WHERE</span> warehouse <span class="token operator">=</span> <span class="token string">'供电局 1# 仓库'</span>
<span class="token keyword">UNION</span>
<span class="token keyword">SELECT</span> <span class="token operator">*</span>
<span class="token keyword">FROM</span> Stock
<span class="token keyword">WHERE</span> unit <span class="token operator">&lt;=</span> <span class="token number">50</span><span class="token punctuation">;</span><span aria-hidden="true" class="line-numbers-rows"><span></span><span></span><span></span><span></span><span></span><span></span><span></span></span></code></pre>
<hr>
<h2 id="3-4-数据更新"><a href="#3-4-数据更新" class="headerlink" title="3.4 数据更新"></a>3.4 数据更新</h2><h3 id="3-4-1-插入数据"><a href="#3-4-1-插入数据" class="headerlink" title="3.4.1 插入数据"></a>3.4.1 插入数据</h3><pre class="line-numbers language-sql" data-language="sql"><code class="language-sql"><span class="token keyword">INSERT</span>
<span class="token keyword">INTO</span> 表名 <span class="token punctuation">[</span>属性列<span class="token number">1</span><span class="token punctuation">,</span>属性列<span class="token number">2.</span><span class="token punctuation">.</span><span class="token punctuation">.</span><span class="token punctuation">]</span>
<span class="token keyword">VALUES</span> <span class="token punctuation">(</span>常量<span class="token number">1</span><span class="token punctuation">,</span>常量<span class="token number">2.</span><span class="token punctuation">.</span><span class="token punctuation">.</span><span class="token punctuation">)</span><span class="token punctuation">;</span><span aria-hidden="true" class="line-numbers-rows"><span></span><span></span><span></span></span></code></pre>
<h4 id="插入子查询结果"><a href="#插入子查询结果" class="headerlink" title="插入子查询结果"></a>插入子查询结果</h4><pre class="line-numbers language-sql" data-language="sql"><code class="language-sql"><span class="token keyword">INSERT</span>
<span class="token keyword">INTO</span> 表名 <span class="token punctuation">[</span>属性列<span class="token number">1</span><span class="token punctuation">,</span>属性列<span class="token number">2.</span><span class="token punctuation">.</span><span class="token punctuation">.</span><span class="token punctuation">]</span>
子查询<span class="token punctuation">;</span><span aria-hidden="true" class="line-numbers-rows"><span></span><span></span><span></span></span></code></pre>
<h3 id="3-4-2-修改数据"><a href="#3-4-2-修改数据" class="headerlink" title="3.4.2 修改数据"></a>3.4.2 修改数据</h3><pre class="line-numbers language-sql" data-language="sql"><code class="language-sql"><span class="token keyword">UPDATE</span>
<span class="token keyword">SET</span> 列名 <span class="token operator">=</span> 表达式
<span class="token punctuation">(</span><span class="token keyword">WHERE</span> 条件<span class="token punctuation">)</span> <span class="token punctuation">;</span>
<span class="token comment">---WHERE 缺省表示要修改表中所有元组</span><span aria-hidden="true" class="line-numbers-rows"><span></span><span></span><span></span><span></span></span></code></pre>
<blockquote>
<p>将编号为 “m020” 的物资单价改为 44.5</p>
</blockquote>
<pre class="line-numbers language-sql" data-language="sql"><code class="language-sql"><span class="token keyword">UPDATE</span>
<span class="token keyword">SET</span> unit <span class="token operator">=</span> <span class="token number">44.5</span>
<span class="token keyword">WHERE</span> mat_num <span class="token operator">=</span> m020<span class="token punctuation">;</span><span aria-hidden="true" class="line-numbers-rows"><span></span><span></span><span></span></span></code></pre>
<blockquote>
<p>例3.66 将所有物资的单价增加 1</p>
</blockquote>
<pre class="line-numbers language-sql" data-language="sql"><code class="language-sql"><span class="token keyword">UPDATE</span> Stock
<span class="token keyword">SET</span> unit <span class="token operator">=</span> unit <span class="token operator">+</span> <span class="token number">1</span><span class="token punctuation">;</span><span aria-hidden="true" class="line-numbers-rows"><span></span><span></span></span></code></pre>
<h3 id="3-4-3-删除数据"><a href="#3-4-3-删除数据" class="headerlink" title="3.4.3 删除数据"></a>3.4.3 删除数据</h3><pre class="line-numbers language-sql" data-language="sql"><code class="language-sql"><span class="token keyword">DELETE</span>
<span class="token keyword">FROM</span> 表名
<span class="token punctuation">(</span><span class="token keyword">WHERE</span> 条件<span class="token punctuation">)</span> <span class="token punctuation">;</span>
<span class="token comment">---WHERE 省略表示删除表中所有元组</span><span aria-hidden="true" class="line-numbers-rows"><span></span><span></span><span></span><span></span></span></code></pre>
<blockquote>
<p>例3.69 删除所有抢修工程的领料出库记录</p>
</blockquote>
<pre class="line-numbers language-sql" data-language="sql"><code class="language-sql"><span class="token keyword">DELETE</span>
<span class="token keyword">FROM</span> out_stock<span class="token punctuation">;</span><span aria-hidden="true" class="line-numbers-rows"><span></span><span></span></span></code></pre>
<hr>
<h2 id="3-5-视图"><a href="#3-5-视图" class="headerlink" title="3.5 视图"></a>3.5 视图</h2><h3 id="定义视图"><a href="#定义视图" class="headerlink" title="定义视图"></a>定义视图</h3><pre class="line-numbers language-sql" data-language="sql"><code class="language-sql"><span class="token keyword">CREATE</span> <span class="token keyword">VIEW</span> 视图名 <span class="token punctuation">[</span>列名<span class="token punctuation">,</span>列名<span class="token punctuation">.</span><span class="token punctuation">.</span><span class="token punctuation">.</span><span class="token punctuation">]</span>
<span class="token keyword">AS</span> 子查询
<span class="token punctuation">[</span>WITN <span class="token keyword">CHECK</span> <span class="token keyword">OPTION</span><span class="token punctuation">]</span> <span class="token punctuation">;</span>
<span class="token comment">---子查询可以是任意的 SELECT 语句,但不允许含有 ORDER BY 子句</span>
<span class="token comment">---组成视图的属性列名要么全部省略,要么全部指定</span><span aria-hidden="true" class="line-numbers-rows"><span></span><span></span><span></span><span></span><span></span></span></code></pre>
<blockquote>
<p>例3.71 建立供电局 1# 仓库所存放物资的视图</p>
</blockquote>
<pre class="line-numbers language-sql" data-language="sql"><code class="language-sql"><span class="token keyword">CREATE</span> <span class="token keyword">VIEW</span> s1_stock
<span class="token keyword">AS</span>
 <span class="token keyword">SELECT</span> mat_num<span class="token punctuation">,</span>mat_name<span class="token punctuation">,</span>speci<span class="token punctuation">,</span>amount<span class="token punctuation">,</span>unit
 <span class="token keyword">FROM</span> Stock
 <span class="token keyword">WHERE</span> warehouse <span class="token operator">=</span> <span class="token string">'供电局 1# 仓库'</span><span class="token punctuation">;</span><span aria-hidden="true" class="line-numbers-rows"><span></span><span></span><span></span><span></span><span></span></span></code></pre>
<h3 id="删除视图"><a href="#删除视图" class="headerlink" title="删除视图"></a>删除视图</h3><pre class="line-numbers language-sql" data-language="sql"><code class="language-sql"><span class="token keyword">DROP</span> <span class="token keyword">VIEW</span> 视图名<span class="token punctuation">;</span><span aria-hidden="true" class="line-numbers-rows"><span></span></span></code></pre>
<h3 id="查询视图"><a href="#查询视图" class="headerlink" title="查询视图"></a>查询视图</h3><pre class="line-numbers language-none"><code class="language-none">视图消解法
对视图的查询实际上就是对基本表的查询<span aria-hidden="true" class="line-numbers-rows"><span></span><span></span></span></code></pre>
<h3 id="更新视图"><a href="#更新视图" class="headerlink" title="更新视图"></a>更新视图</h3><pre class="line-numbers language-none"><code class="language-none">插入 INSERT
删除 DELETE
修改 UPDATE<span aria-hidden="true" class="line-numbers-rows"><span></span><span></span><span></span></span></code></pre>
<hr>
<h1 id="第四章"><a href="#第四章" class="headerlink" title="第四章"></a>第四章</h1><h2 id="4-2-存储过程"><a href="#4-2-存储过程" class="headerlink" title="4.2 存储过程"></a>4.2 存储过程</h2><h3 id="创建存储过程"><a href="#创建存储过程" class="headerlink" title="创建存储过程"></a>创建存储过程</h3><pre class="line-numbers language-sql" data-language="sql"><code class="language-sql"><span class="token keyword">CREATE</span> <span class="token keyword">PROCEDURE</span> 存储过程名
<span class="token keyword">AS</span>
 <span class="token keyword">SQL</span> 语句<span aria-hidden="true" class="line-numbers-rows"><span></span><span></span><span></span></span></code></pre>
<h3 id="修改存储过程"><a href="#修改存储过程" class="headerlink" title="修改存储过程"></a>修改存储过程</h3><pre class="line-numbers language-sql" data-language="sql"><code class="language-sql"><span class="token keyword">ALTER</span> <span class="token keyword">PROCEDURE</span> 存储过程名
<span class="token keyword">AS</span>
 <span class="token keyword">SQL</span> 语句<span aria-hidden="true" class="line-numbers-rows"><span></span><span></span><span></span></span></code></pre>
<h3 id="删除存储过程"><a href="#删除存储过程" class="headerlink" title="删除存储过程"></a>删除存储过程</h3><pre class="line-numbers language-sql" data-language="sql"><code class="language-sql"><span class="token keyword">DROP</span> <span class="token keyword">PROCEDURE</span> 存储过程名<span aria-hidden="true" class="line-numbers-rows"><span></span></span></code></pre>
<h2 id="4-3-触发器"><a href="#4-3-触发器" class="headerlink" title="4.3 触发器"></a>4.3 触发器</h2><pre class="line-numbers language-none"><code class="language-none">AFTER 触发器
INSTEAD OF 触发器<span aria-hidden="true" class="line-numbers-rows"><span></span><span></span></span></code></pre>
<h3 id="创建触发器"><a href="#创建触发器" class="headerlink" title="创建触发器"></a>创建触发器</h3><pre class="line-numbers language-sql" data-language="sql"><code class="language-sql"><span class="token keyword">CREATE</span> <span class="token keyword">TRIGGER</span> 触发器名
<span class="token keyword">ON</span> 表名<span class="token operator">|</span>视图名
<span class="token keyword">AS</span>
 <span class="token keyword">SQL</span> 语句<span aria-hidden="true" class="line-numbers-rows"><span></span><span></span><span></span><span></span></span></code></pre>
<h1 id="第五章"><a href="#第五章" class="headerlink" title="第五章"></a>第五章</h1><h3 id="定义-1"><a href="#定义-1" class="headerlink" title="定义"></a>定义</h3><pre class="line-numbers language-none"><code class="language-none">包含在任何一个候选码中的属性称为 主属性
不包含在任何码中的属性称为 非主属性或非码属性
主属性是表格的第一列
规范化:消除关系模式中的数据冗余<span aria-hidden="true" class="line-numbers-rows"><span></span><span></span><span></span><span></span></span></code></pre>
<h3 id="第一范式（1NF）"><a href="#第一范式（1NF）" class="headerlink" title="第一范式（1NF）"></a>第一范式（1NF）</h3><pre class="line-numbers language-none"><code class="language-none">一个关系模式R的所有属性均为简单属性,即每个属性都是不可再分的,记作R∈1NF<span aria-hidden="true" class="line-numbers-rows"><span></span></span></code></pre>
<h3 id="第二范式（2NF）"><a href="#第二范式（2NF）" class="headerlink" title="第二范式（2NF）"></a>第二范式（2NF）</h3><pre class="line-numbers language-none"><code class="language-none">满足第一范式,且所有非主属性都完全依赖于主码
即:消除非主属性对主属性的部份依赖<span aria-hidden="true" class="line-numbers-rows"><span></span><span></span></span></code></pre>
<h3 id="第三范式（3NF）"><a href="#第三范式（3NF）" class="headerlink" title="第三范式（3NF）"></a>第三范式（3NF）</h3><pre class="line-numbers language-none"><code class="language-none">满足第二范式,且所有非主属性对任何候选码都不存在传递依赖
即:消除非主属性对主属性的部份依赖和传递依赖<span aria-hidden="true" class="line-numbers-rows"><span></span><span></span></span></code></pre>
<hr>
<h1 id="第六章-索引"><a href="#第六章-索引" class="headerlink" title="第六章 索引"></a>第六章 索引</h1><h3 id="聚集索引"><a href="#聚集索引" class="headerlink" title="聚集索引"></a>聚集索引</h3><pre class="line-numbers language-none"><code class="language-none">数据文件中的记录按照索引键指定的顺序排序
一个数据表只能建立一个聚集索引<span aria-hidden="true" class="line-numbers-rows"><span></span><span></span></span></code></pre>
<h3 id="非聚集索引"><a href="#非聚集索引" class="headerlink" title="非聚集索引"></a>非聚集索引</h3><pre class="line-numbers language-none"><code class="language-none">一个表可以有多个非聚集索引<span aria-hidden="true" class="line-numbers-rows"><span></span></span></code></pre>
<h3 id="唯一索引"><a href="#唯一索引" class="headerlink" title="唯一索引"></a>唯一索引</h3><pre class="line-numbers language-none"><code class="language-none">不允许表中任何两行具有相同索引值的索引<span aria-hidden="true" class="line-numbers-rows"><span></span></span></code></pre>
<h3 id="创建索引"><a href="#创建索引" class="headerlink" title="创建索引"></a>创建索引</h3><pre class="line-numbers language-sql" data-language="sql"><code class="language-sql"><span class="token keyword">CREATE</span> 索引类型
<span class="token keyword">INDEX</span> 索引名称 <span class="token keyword">ON</span> 用于创建索引的表或视图 <span class="token punctuation">(</span>用于创建索引的列 指定索引列的排列方式<span class="token punctuation">,</span><span class="token keyword">ASC</span>升序、<span class="token keyword">DESC</span>降序<span class="token punctuation">)</span>
<span class="token comment">--- UNIQUE :创建一个唯一索引</span>
<span class="token comment">--- CLUSTERED :聚集索引</span>
<span class="token comment">--- NONCLUSTERED :非聚集索引</span><span aria-hidden="true" class="line-numbers-rows"><span></span><span></span><span></span><span></span><span></span></span></code></pre>
<h3 id="查看索引"><a href="#查看索引" class="headerlink" title="查看索引"></a>查看索引</h3><pre class="line-numbers language-sql" data-language="sql"><code class="language-sql"><span class="token keyword">USE</span> 数据库名
<span class="token keyword">Exec</span>  sp_helpindex 所查表<span aria-hidden="true" class="line-numbers-rows"><span></span><span></span></span></code></pre>
<h3 id="删除索引"><a href="#删除索引" class="headerlink" title="删除索引"></a>删除索引</h3><pre class="line-numbers language-sql" data-language="sql"><code class="language-sql"><span class="token keyword">DROP</span> <span class="token keyword">INDEX</span> 索引名<span aria-hidden="true" class="line-numbers-rows"><span></span></span></code></pre>
<hr>
<h1 id="第七章-E-R图"><a href="#第七章-E-R图" class="headerlink" title="第七章 E-R图"></a>第七章 E-R图</h1><h3 id="实体-联系模型-E-R模型"><a href="#实体-联系模型-E-R模型" class="headerlink" title="实体-联系模型(E-R模型)"></a>实体-联系模型(E-R模型)</h3><pre class="line-numbers language-none"><code class="language-none">实体可以是物理存在的事物,也可以是抽象的概念
每个实体都有一组特征或性质,称为实体的属性
由一个属性构成的键称为简单键
由多个属性构成的键称为复合键<span aria-hidden="true" class="line-numbers-rows"><span></span><span></span><span></span><span></span></span></code></pre>
<h3 id="实体间的联系"><a href="#实体间的联系" class="headerlink" title="实体间的联系"></a>实体间的联系</h3><pre class="line-numbers language-none"><code class="language-none">一对一联系(1:1)
一对多联系(1:n)
多对多联系(m:n)<span aria-hidden="true" class="line-numbers-rows"><span></span><span></span><span></span></span></code></pre>
<h3 id="实体-联系图"><a href="#实体-联系图" class="headerlink" title="实体-联系图"></a>实体-联系图</h3><pre class="line-numbers language-none"><code class="language-none">(1).实体集:用矩形表示,矩形框内写明实体名
(2).属性:用椭圆表示,并用无向边将其与相应的实体集连接起来;
     多值属性用双层的椭圆表示
     主键加下划线
(3).联系:用菱形表示,菱形框内写明联系的名称,并用无向边分别与有关实体集连接起来;同时在无向边上表明联系的类型(1:1、1:n、m:n)<span aria-hidden="true" class="line-numbers-rows"><span></span><span></span><span></span><span></span><span></span></span></code></pre></article><div class="post-copyright"><div class="post-copyright__author"><span class="post-copyright-meta">文章作者: </span><span class="post-copyright-info"><a href="https://liy002.gitee.io">星Z痕</a></span></div><div class="post-copyright__type"><span class="post-copyright-meta">文章链接: </span><span class="post-copyright-info"><a href="https://liy002.gitee.io/2022/01/18/sql/">https://liy002.gitee.io/2022/01/18/sql/</a></span></div><div class="post-copyright__notice"><span class="post-copyright-meta">版权声明: </span><span class="post-copyright-info">本博客所有文章除特别声明外，均采用 <a href="https://creativecommons.org/licenses/by-nc-sa/4.0/" target="_blank">CC BY-NC-SA 4.0</a> 许可协议。转载请注明来自 <a href="https://liy002.gitee.io" target="_blank">星Z痕</a>！</span></div></div><div class="tag_share"><div class="post-meta__tag-list"><a class="post-meta__tags" href="/tags/%E7%AC%94%E8%AE%B0/">笔记</a><a class="post-meta__tags" href="/tags/%E6%95%B0%E6%8D%AE%E5%BA%93/">数据库</a></div><div class="post_share"><div class="social-share" data-image="https://www.helloimg.com/images/2022/09/24/Z89nc6.png" data-sites="facebook,twitter,wechat,weibo,qq"></div><link rel="stylesheet" href="https://cdn.jsdelivr.net/npm/social-share.js/dist/css/share.min.css" media="print" onload="this.media='all'"><script src="https://cdn.jsdelivr.net/gh/overtrue/share.js@master/dist/js/social-share.min.js" defer></script></div></div><nav class="pagination-post" id="pagination"><div class="prev-post pull-left"><a href="/2022/01/18/java-ti/"><img class="prev-cover" src="https://www.helloimg.com/images/2022/09/24/Z89nc6.png" onerror="onerror=null;src='/img/404.jpg'" alt="cover of previous post"><div class="pagination-info"><div class="label">上一篇</div><div class="prev_info">Java-题</div></div></a></div><div class="next-post pull-right"><a href="/2022/01/18/suan-fa-fen-xi/"><img class="next-cover" src="https://www.helloimg.com/images/2022/05/22/ZRhIlo.png" onerror="onerror=null;src='/img/404.jpg'" alt="cover of next post"><div class="pagination-info"><div class="label">下一篇</div><div class="next_info">计算机算法设计与分析</div></div></a></div></nav><div class="relatedPosts"><div class="headline"><i class="fas fa-thumbs-up fa-fw"></i><span>相关推荐</span></div><div class="relatedPosts-list"><div><a href="/2022/01/19/html/" title="HTML"><img class="cover" src="https://www.helloimg.com/images/2022/09/24/Z89nc6.png" alt="cover"><div class="content is-center"><div class="date"><i class="far fa-calendar-alt fa-fw"></i> 2022-01-19</div><div class="title">HTML</div></div></a></div><div><a href="/2022/01/18/java-ti/" title="Java-题"><img class="cover" src="https://www.helloimg.com/images/2022/09/24/Z89nc6.png" alt="cover"><div class="content is-center"><div class="date"><i class="far fa-calendar-alt fa-fw"></i> 2022-01-18</div><div class="title">Java-题</div></div></a></div><div><a href="/2022/01/18/suan-fa-fen-xi/" title="计算机算法设计与分析"><img class="cover" src="https://www.helloimg.com/images/2022/05/22/ZRhIlo.png" alt="cover"><div class="content is-center"><div class="date"><i class="far fa-calendar-alt fa-fw"></i> 2022-01-18</div><div class="title">计算机算法设计与分析</div></div></a></div><div><a href="/2022/01/18/ji-suan-ji-tu-xing-xue/" title="计算机图形学"><img class="cover" src="https://www.helloimg.com/images/2022/09/25/Z80JM6.jpg" alt="cover"><div class="content is-center"><div class="date"><i class="far fa-calendar-alt fa-fw"></i> 2022-01-18</div><div class="title">计算机图形学</div></div></a></div></div></div><hr/><div id="post-comment"><div class="comment-head"><div class="comment-headline"><i class="fas fa-comments fa-fw"></i><span> 评论</span></div></div><div class="comment-wrap"><div><div id="twikoo-wrap"></div></div></div></div></div><div class="aside-content" id="aside-content"><div class="card-widget card-info"><div class="is-center"><div class="avatar-img"><img src="/img/%E5%A4%B4%E5%83%8F.jpg" onerror="this.onerror=null;this.src='/img/friend_404.gif'" alt="avatar"/></div><div class="author-info__name">星Z痕</div><div class="author-info__description">明德 修业 砺志 笃行</div></div><div class="card-info-data site-data is-center"><a href="/archives/"><div class="headline">文章</div><div class="length-num">20</div></a><a href="/tags/"><div class="headline">标签</div><div class="length-num">12</div></a><a href="/categories/"><div class="headline">分类</div><div class="length-num">1</div></a></div><div class="card-info-social-icons is-center"><a class="social-icon" href="https://github.com/LiY001" target="_blank" title="Github"><i class="fab fa-github"></i></a><a class="social-icon" href="mailto:2477577791@qq.com" target="_blank" title="Email"><i class="fas fa-envelope"></i></a><a class="social-icon" href="https://www.helloimg.com/images/2022/05/09/RNS7Eg.png" target="_blank" title="QQ"><i class="fab fa-qq"></i></a></div></div><div class="sticky_layout"><div class="card-widget" id="card-toc"><div class="item-headline"><i class="fas fa-stream"></i><span>目录</span><span class="toc-percentage"></span></div><div class="toc-content"><ol class="toc"><li class="toc-item toc-level-1"><a class="toc-link" href="#%E7%AC%AC%E4%B8%80%E7%AB%A0"><span class="toc-number">1.</span> <span class="toc-text">第一章</span></a><ol class="toc-child"><li class="toc-item toc-level-2"><a class="toc-link" href="#%E6%95%B0%E6%8D%AE%E5%BA%93%E7%B3%BB%E7%BB%9F%E4%B8%89%E7%BA%A7%E7%BB%93%E6%9E%84%E6%A8%A1%E5%BC%8F"><span class="toc-number">1.1.</span> <span class="toc-text">数据库系统三级结构模式</span></a></li></ol></li><li class="toc-item toc-level-1"><a class="toc-link" href="#%E7%AC%AC%E4%BA%8C%E7%AB%A0-%E5%85%B3%E7%B3%BB%E6%95%B0%E6%8D%AE%E5%BA%93"><span class="toc-number">2.</span> <span class="toc-text">第二章 关系数据库</span></a><ol class="toc-child"><li class="toc-item toc-level-3"><a class="toc-link" href="#%E5%AE%9A%E4%B9%89"><span class="toc-number">2.0.1.</span> <span class="toc-text">定义</span></a></li><li class="toc-item toc-level-3"><a class="toc-link" href="#%E5%85%B3%E7%B3%BB%E4%BB%A3%E6%95%B0"><span class="toc-number">2.0.2.</span> <span class="toc-text">关系代数</span></a><ol class="toc-child"><li class="toc-item toc-level-4"><a class="toc-link" href="#%E5%A4%96%E8%BF%9E%E6%8E%A5"><span class="toc-number">2.0.2.1.</span> <span class="toc-text">外连接</span></a></li></ol></li></ol></li></ol></li><li class="toc-item toc-level-1"><a class="toc-link" href="#%E7%AC%AC%E4%B8%89%E7%AB%A0"><span class="toc-number">3.</span> <span class="toc-text">第三章</span></a><ol class="toc-child"><li class="toc-item toc-level-2"><a class="toc-link" href="#3-3-%E6%9F%A5%E8%AF%A2"><span class="toc-number">3.1.</span> <span class="toc-text">3.3 查询</span></a><ol class="toc-child"><li class="toc-item toc-level-4"><a class="toc-link" href="#%E5%8E%BB%E6%8E%89%E8%A1%A8%E4%B8%AD%E7%9A%84%E9%87%8D%E5%A4%8D%E8%A1%8C"><span class="toc-number">3.1.0.1.</span> <span class="toc-text">去掉表中的重复行</span></a></li><li class="toc-item toc-level-4"><a class="toc-link" href="#%E7%A1%AE%E5%AE%9A%E8%8C%83%E5%9B%B4%E7%9A%84%E6%9F%A5%E8%AF%A2"><span class="toc-number">3.1.0.2.</span> <span class="toc-text">确定范围的查询</span></a></li><li class="toc-item toc-level-4"><a class="toc-link" href="#%E5%AD%97%E7%AC%A6%E5%8C%B9%E9%85%8D%E7%9A%84%E6%9F%A5%E8%AF%A2"><span class="toc-number">3.1.0.3.</span> <span class="toc-text">字符匹配的查询</span></a></li><li class="toc-item toc-level-4"><a class="toc-link" href="#3-%E5%AF%B9%E6%9F%A5%E8%AF%A2%E7%BB%93%E6%9E%9C%E8%BF%9B%E8%A1%8C%E6%8E%92%E5%BA%8F"><span class="toc-number">3.1.0.4.</span> <span class="toc-text">3. 对查询结果进行排序</span></a></li><li class="toc-item toc-level-4"><a class="toc-link" href="#4-TOP-%E5%AD%90%E5%8F%A5%E7%9A%84%E7%94%A8%E6%B3%95"><span class="toc-number">3.1.0.5.</span> <span class="toc-text">4. TOP 子句的用法</span></a></li><li class="toc-item toc-level-4"><a class="toc-link" href="#5-%E8%81%9A%E9%9B%86%E5%87%BD%E6%95%B0"><span class="toc-number">3.1.0.6.</span> <span class="toc-text">5. 聚集函数</span></a></li></ol></li><li class="toc-item toc-level-3"><a class="toc-link" href="#3-3-2-%E8%BF%9E%E6%8E%A5%E6%9F%A5%E8%AF%A2"><span class="toc-number">3.1.1.</span> <span class="toc-text">3.3.2 连接查询</span></a><ol class="toc-child"><li class="toc-item toc-level-4"><a class="toc-link" href="#%E7%AD%89%E5%80%BC%E4%B8%8E%E9%9D%9E%E7%AD%89%E5%80%BC%E8%BF%9E%E6%8E%A5%E6%9F%A5%E8%AF%A2"><span class="toc-number">3.1.1.1.</span> <span class="toc-text">等值与非等值连接查询</span></a></li><li class="toc-item toc-level-4"><a class="toc-link" href="#%E5%A4%96%E8%BF%9E%E6%8E%A5%E6%9F%A5%E8%AF%A2"><span class="toc-number">3.1.1.2.</span> <span class="toc-text">外连接查询</span></a></li><li class="toc-item toc-level-4"><a class="toc-link" href="#%E5%A4%8D%E5%90%88%E6%9D%A1%E4%BB%B6%E8%BF%9E%E6%8E%A5%E6%9F%A5%E8%AF%A2"><span class="toc-number">3.1.1.3.</span> <span class="toc-text">复合条件连接查询</span></a></li><li class="toc-item toc-level-4"><a class="toc-link" href="#%E8%87%AA%E8%BA%AB%E8%BF%9E%E6%8E%A5%E6%9F%A5%E8%AF%A2"><span class="toc-number">3.1.1.4.</span> <span class="toc-text">自身连接查询</span></a></li></ol></li><li class="toc-item toc-level-3"><a class="toc-link" href="#3-3-3-%E5%B5%8C%E5%A5%97%E6%9F%A5%E8%AF%A2"><span class="toc-number">3.1.2.</span> <span class="toc-text">3.3.3 嵌套查询</span></a><ol class="toc-child"><li class="toc-item toc-level-4"><a class="toc-link" href="#%E5%B8%A6%E8%B0%93%E8%AF%8D-IN-%E7%9A%84%E5%B5%8C%E5%A5%97%E6%9F%A5%E8%AF%A2"><span class="toc-number">3.1.2.1.</span> <span class="toc-text">带谓词 IN 的嵌套查询</span></a></li><li class="toc-item toc-level-4"><a class="toc-link" href="#%E5%B8%A6%E6%9C%89-ANY-%E6%88%96-ALL-%E8%B0%93%E8%AF%8D%E7%9A%84%E5%AD%90%E6%9F%A5%E8%AF%A2"><span class="toc-number">3.1.2.2.</span> <span class="toc-text">带有 ANY 或 ALL 谓词的子查询</span></a></li></ol></li><li class="toc-item toc-level-3"><a class="toc-link" href="#3-3-4-%E9%9B%86%E5%90%88%E6%9F%A5%E8%AF%A2"><span class="toc-number">3.1.3.</span> <span class="toc-text">3.3.4. 集合查询</span></a></li></ol></li><li class="toc-item toc-level-2"><a class="toc-link" href="#3-4-%E6%95%B0%E6%8D%AE%E6%9B%B4%E6%96%B0"><span class="toc-number">3.2.</span> <span class="toc-text">3.4 数据更新</span></a><ol class="toc-child"><li class="toc-item toc-level-3"><a class="toc-link" href="#3-4-1-%E6%8F%92%E5%85%A5%E6%95%B0%E6%8D%AE"><span class="toc-number">3.2.1.</span> <span class="toc-text">3.4.1 插入数据</span></a><ol class="toc-child"><li class="toc-item toc-level-4"><a class="toc-link" href="#%E6%8F%92%E5%85%A5%E5%AD%90%E6%9F%A5%E8%AF%A2%E7%BB%93%E6%9E%9C"><span class="toc-number">3.2.1.1.</span> <span class="toc-text">插入子查询结果</span></a></li></ol></li><li class="toc-item toc-level-3"><a class="toc-link" href="#3-4-2-%E4%BF%AE%E6%94%B9%E6%95%B0%E6%8D%AE"><span class="toc-number">3.2.2.</span> <span class="toc-text">3.4.2 修改数据</span></a></li><li class="toc-item toc-level-3"><a class="toc-link" href="#3-4-3-%E5%88%A0%E9%99%A4%E6%95%B0%E6%8D%AE"><span class="toc-number">3.2.3.</span> <span class="toc-text">3.4.3 删除数据</span></a></li></ol></li><li class="toc-item toc-level-2"><a class="toc-link" href="#3-5-%E8%A7%86%E5%9B%BE"><span class="toc-number">3.3.</span> <span class="toc-text">3.5 视图</span></a><ol class="toc-child"><li class="toc-item toc-level-3"><a class="toc-link" href="#%E5%AE%9A%E4%B9%89%E8%A7%86%E5%9B%BE"><span class="toc-number">3.3.1.</span> <span class="toc-text">定义视图</span></a></li><li class="toc-item toc-level-3"><a class="toc-link" href="#%E5%88%A0%E9%99%A4%E8%A7%86%E5%9B%BE"><span class="toc-number">3.3.2.</span> <span class="toc-text">删除视图</span></a></li><li class="toc-item toc-level-3"><a class="toc-link" href="#%E6%9F%A5%E8%AF%A2%E8%A7%86%E5%9B%BE"><span class="toc-number">3.3.3.</span> <span class="toc-text">查询视图</span></a></li><li class="toc-item toc-level-3"><a class="toc-link" href="#%E6%9B%B4%E6%96%B0%E8%A7%86%E5%9B%BE"><span class="toc-number">3.3.4.</span> <span class="toc-text">更新视图</span></a></li></ol></li></ol></li><li class="toc-item toc-level-1"><a class="toc-link" href="#%E7%AC%AC%E5%9B%9B%E7%AB%A0"><span class="toc-number">4.</span> <span class="toc-text">第四章</span></a><ol class="toc-child"><li class="toc-item toc-level-2"><a class="toc-link" href="#4-2-%E5%AD%98%E5%82%A8%E8%BF%87%E7%A8%8B"><span class="toc-number">4.1.</span> <span class="toc-text">4.2 存储过程</span></a><ol class="toc-child"><li class="toc-item toc-level-3"><a class="toc-link" href="#%E5%88%9B%E5%BB%BA%E5%AD%98%E5%82%A8%E8%BF%87%E7%A8%8B"><span class="toc-number">4.1.1.</span> <span class="toc-text">创建存储过程</span></a></li><li class="toc-item toc-level-3"><a class="toc-link" href="#%E4%BF%AE%E6%94%B9%E5%AD%98%E5%82%A8%E8%BF%87%E7%A8%8B"><span class="toc-number">4.1.2.</span> <span class="toc-text">修改存储过程</span></a></li><li class="toc-item toc-level-3"><a class="toc-link" href="#%E5%88%A0%E9%99%A4%E5%AD%98%E5%82%A8%E8%BF%87%E7%A8%8B"><span class="toc-number">4.1.3.</span> <span class="toc-text">删除存储过程</span></a></li></ol></li><li class="toc-item toc-level-2"><a class="toc-link" href="#4-3-%E8%A7%A6%E5%8F%91%E5%99%A8"><span class="toc-number">4.2.</span> <span class="toc-text">4.3 触发器</span></a><ol class="toc-child"><li class="toc-item toc-level-3"><a class="toc-link" href="#%E5%88%9B%E5%BB%BA%E8%A7%A6%E5%8F%91%E5%99%A8"><span class="toc-number">4.2.1.</span> <span class="toc-text">创建触发器</span></a></li></ol></li></ol></li><li class="toc-item toc-level-1"><a class="toc-link" href="#%E7%AC%AC%E4%BA%94%E7%AB%A0"><span class="toc-number">5.</span> <span class="toc-text">第五章</span></a><ol class="toc-child"><li class="toc-item toc-level-3"><a class="toc-link" href="#%E5%AE%9A%E4%B9%89-1"><span class="toc-number">5.0.1.</span> <span class="toc-text">定义</span></a></li><li class="toc-item toc-level-3"><a class="toc-link" href="#%E7%AC%AC%E4%B8%80%E8%8C%83%E5%BC%8F%EF%BC%881NF%EF%BC%89"><span class="toc-number">5.0.2.</span> <span class="toc-text">第一范式（1NF）</span></a></li><li class="toc-item toc-level-3"><a class="toc-link" href="#%E7%AC%AC%E4%BA%8C%E8%8C%83%E5%BC%8F%EF%BC%882NF%EF%BC%89"><span class="toc-number">5.0.3.</span> <span class="toc-text">第二范式（2NF）</span></a></li><li class="toc-item toc-level-3"><a class="toc-link" href="#%E7%AC%AC%E4%B8%89%E8%8C%83%E5%BC%8F%EF%BC%883NF%EF%BC%89"><span class="toc-number">5.0.4.</span> <span class="toc-text">第三范式（3NF）</span></a></li></ol></li></ol></li><li class="toc-item toc-level-1"><a class="toc-link" href="#%E7%AC%AC%E5%85%AD%E7%AB%A0-%E7%B4%A2%E5%BC%95"><span class="toc-number">6.</span> <span class="toc-text">第六章 索引</span></a><ol class="toc-child"><li class="toc-item toc-level-3"><a class="toc-link" href="#%E8%81%9A%E9%9B%86%E7%B4%A2%E5%BC%95"><span class="toc-number">6.0.1.</span> <span class="toc-text">聚集索引</span></a></li><li class="toc-item toc-level-3"><a class="toc-link" href="#%E9%9D%9E%E8%81%9A%E9%9B%86%E7%B4%A2%E5%BC%95"><span class="toc-number">6.0.2.</span> <span class="toc-text">非聚集索引</span></a></li><li class="toc-item toc-level-3"><a class="toc-link" href="#%E5%94%AF%E4%B8%80%E7%B4%A2%E5%BC%95"><span class="toc-number">6.0.3.</span> <span class="toc-text">唯一索引</span></a></li><li class="toc-item toc-level-3"><a class="toc-link" href="#%E5%88%9B%E5%BB%BA%E7%B4%A2%E5%BC%95"><span class="toc-number">6.0.4.</span> <span class="toc-text">创建索引</span></a></li><li class="toc-item toc-level-3"><a class="toc-link" href="#%E6%9F%A5%E7%9C%8B%E7%B4%A2%E5%BC%95"><span class="toc-number">6.0.5.</span> <span class="toc-text">查看索引</span></a></li><li class="toc-item toc-level-3"><a class="toc-link" href="#%E5%88%A0%E9%99%A4%E7%B4%A2%E5%BC%95"><span class="toc-number">6.0.6.</span> <span class="toc-text">删除索引</span></a></li></ol></li></ol></li><li class="toc-item toc-level-1"><a class="toc-link" href="#%E7%AC%AC%E4%B8%83%E7%AB%A0-E-R%E5%9B%BE"><span class="toc-number">7.</span> <span class="toc-text">第七章 E-R图</span></a><ol class="toc-child"><li class="toc-item toc-level-3"><a class="toc-link" href="#%E5%AE%9E%E4%BD%93-%E8%81%94%E7%B3%BB%E6%A8%A1%E5%9E%8B-E-R%E6%A8%A1%E5%9E%8B"><span class="toc-number">7.0.1.</span> <span class="toc-text">实体-联系模型(E-R模型)</span></a></li><li class="toc-item toc-level-3"><a class="toc-link" href="#%E5%AE%9E%E4%BD%93%E9%97%B4%E7%9A%84%E8%81%94%E7%B3%BB"><span class="toc-number">7.0.2.</span> <span class="toc-text">实体间的联系</span></a></li><li class="toc-item toc-level-3"><a class="toc-link" href="#%E5%AE%9E%E4%BD%93-%E8%81%94%E7%B3%BB%E5%9B%BE"><span class="toc-number">7.0.3.</span> <span class="toc-text">实体-联系图</span></a></li></ol></li></ol></li></ol></div></div><div class="card-widget card-recent-post"><div class="item-headline"><i class="fas fa-history"></i><span>最新文章</span></div><div class="aside-list"><div class="aside-list-item"><a class="thumbnail" href="/2022/09/13/hei-ma-java/" title="JAVA"><img src="https://www.helloimg.com/images/2022/09/25/Z80JM6.jpg" onerror="this.onerror=null;this.src='/img/404.jpg'" alt="JAVA"/></a><div class="content"><a class="title" href="/2022/09/13/hei-ma-java/" title="JAVA">JAVA</a><time datetime="2022-09-13T03:24:45.000Z" title="发表于 2022-09-13 11:24:45">2022-09-13</time></div></div><div class="aside-list-item"><a class="thumbnail" href="/2022/06/06/python/" title="Python"><img src="https://www.helloimg.com/images/2022/09/25/Z80JM6.jpg" onerror="this.onerror=null;this.src='/img/404.jpg'" alt="Python"/></a><div class="content"><a class="title" href="/2022/06/06/python/" title="Python">Python</a><time datetime="2022-06-06T04:30:45.000Z" title="发表于 2022-06-06 12:30:45">2022-06-06</time></div></div><div class="aside-list-item"><a class="thumbnail" href="/2022/04/13/bian-yi-yuan-li/" title="编译原理"><img src="https://www.helloimg.com/images/2022/05/22/ZRhIlo.png" onerror="this.onerror=null;this.src='/img/404.jpg'" alt="编译原理"/></a><div class="content"><a class="title" href="/2022/04/13/bian-yi-yuan-li/" title="编译原理">编译原理</a><time datetime="2022-04-13T11:52:04.000Z" title="发表于 2022-04-13 19:52:04">2022-04-13</time></div></div><div class="aside-list-item"><a class="thumbnail" href="/2022/04/11/md-wen-dang-ge-shi/" title="md文档格式"><img src="https://www.helloimg.com/images/2022/05/22/ZRhIlo.png" onerror="this.onerror=null;this.src='/img/404.jpg'" alt="md文档格式"/></a><div class="content"><a class="title" href="/2022/04/11/md-wen-dang-ge-shi/" title="md文档格式">md文档格式</a><time datetime="2022-04-11T11:14:22.000Z" title="发表于 2022-04-11 19:14:22">2022-04-11</time></div></div><div class="aside-list-item"><a class="thumbnail" href="/2022/04/05/su-shu/" title="素数"><img src="https://www.helloimg.com/images/2022/05/22/ZRhIlo.png" onerror="this.onerror=null;this.src='/img/404.jpg'" alt="素数"/></a><div class="content"><a class="title" href="/2022/04/05/su-shu/" title="素数">素数</a><time datetime="2022-04-05T13:18:14.000Z" title="发表于 2022-04-05 21:18:14">2022-04-05</time></div></div></div></div></div></div></main><footer id="footer" style="background-image: url('https://www.helloimg.com/images/2022/09/24/Z89nc6.png')"><div id="footer-wrap"><div class="copyright">&copy;2022 By 星Z痕</div><div class="framework-info"><span>框架 </span><a target="_blank" rel="noopener" href="https://hexo.io">Hexo</a><span class="footer-separator">|</span><span>主题 </span><a target="_blank" rel="noopener" href="https://github.com/jerryc127/hexo-theme-butterfly">Butterfly</a></div></div></footer></div><div id="rightside"><div id="rightside-config-hide"><button id="readmode" type="button" title="阅读模式"><i class="fas fa-book-open"></i></button><button id="darkmode" type="button" title="浅色和深色模式转换"><i class="fas fa-adjust"></i></button><button id="hide-aside-btn" type="button" title="单栏和双栏切换"><i class="fas fa-arrows-alt-h"></i></button></div><div id="rightside-config-show"><button id="rightside_config" type="button" title="设置"><i class="fas fa-cog fa-spin"></i></button><button class="close" id="mobile-toc-button" type="button" title="目录"><i class="fas fa-list-ul"></i></button><a id="to_comment" href="#post-comment" title="直达评论"><i class="fas fa-comments"></i></a><button id="go-up" type="button" title="回到顶部"><i class="fas fa-arrow-up"></i></button></div></div><div id="local-search"><div class="search-dialog"><nav class="search-nav"><span class="search-dialog-title">本地搜索</span><span id="loading-status"></span><button class="search-close-button"><i class="fas fa-times"></i></button></nav><div class="is-center" id="loading-database"><i class="fas fa-spinner fa-pulse"></i><span>  数据库加载中</span></div><div class="search-wrap"><div id="local-search-input"><div class="local-search-box"><input class="local-search-box--input" placeholder="搜索文章" type="text"/></div></div><hr/><div id="local-search-results"></div></div></div><div id="search-mask"></div></div><div><script src="/js/utils.js"></script><script src="/js/main.js"></script><script src="https://cdn.jsdelivr.net/npm/@fancyapps/ui/dist/fancybox.umd.js"></script><script src="/js/search/local-search.js"></script><script>var preloader = {
  endLoading: () => {
    document.body.style.overflow = 'auto';
    document.getElementById('loading-box').classList.add("loaded")
  },
  initLoading: () => {
    document.body.style.overflow = '';
    document.getElementById('loading-box').classList.remove("loaded")

  }
}
window.addEventListener('load',preloader.endLoading())</script><div class="js-pjax"><script>if (!window.MathJax) {
  window.MathJax = {
    tex: {
      inlineMath: [ ['$','$'], ["\\(","\\)"]],
      tags: 'ams'
    },
    chtml: {
      scale: 1.2
    },
    options: {
      renderActions: {
        findScript: [10, doc => {
          for (const node of document.querySelectorAll('script[type^="math/tex"]')) {
            const display = !!node.type.match(/; *mode=display/)
            const math = new doc.options.MathItem(node.textContent, doc.inputJax[0], display)
            const text = document.createTextNode('')
            node.parentNode.replaceChild(text, node)
            math.start = {node: text, delim: '', n: 0}
            math.end = {node: text, delim: '', n: 0}
            doc.math.push(math)
          }
        }, ''],
        insertScript: [200, () => {
          document.querySelectorAll('mjx-container:not\([display]\)').forEach(node => {
            const target = node.parentNode
            if (target.nodeName.toLowerCase() === 'li') {
              target.parentNode.classList.add('has-jax')
            } else {
              target.classList.add('has-jax')
            }
          });
        }, '', false]
      }
    }
  }
  
  const script = document.createElement('script')
  script.src = 'https://cdn.jsdelivr.net/npm/mathjax@3/es5/tex-mml-chtml.js'
  script.id = 'MathJax-script'
  script.async = true
  document.head.appendChild(script)
} else {
  MathJax.startup.document.state(0)
  MathJax.texReset()
  MathJax.typeset()
}</script><script>(()=>{
  const init = () => {
    twikoo.init(Object.assign({
      el: '#twikoo-wrap',
      envId: 'twikoo-9gb03gtz6289e2f8',
      region: '',
      onCommentLoaded: function () {
        btf.loadLightbox(document.querySelectorAll('#twikoo .tk-content img:not(.tk-owo-emotion)'))
      }
    }, null))
  }

  const getCount = () => {
    twikoo.getCommentsCount({
      envId: 'twikoo-9gb03gtz6289e2f8',
      region: '',
      urls: [window.location.pathname],
      includeReply: false
    }).then(function (res) {
      document.getElementById('twikoo-count').innerText = res[0].count
    }).catch(function (err) {
      console.error(err);
    });
  }

  const runFn = () => {
    init()
    
  }

  const loadTwikoo = () => {
    if (typeof twikoo === 'object') {
      setTimeout(runFn,0)
      return
    } 
    getScript('https://cdn.jsdelivr.net/npm/twikoo@1/dist/twikoo.all.min.js').then(runFn)
  }

  if ('Twikoo' === 'Twikoo' || !false) {
    if (false) btf.loadComment(document.getElementById('twikoo-wrap'), loadTwikoo)
    else loadTwikoo()
  } else {
    window.loadOtherComment = () => {
      loadTwikoo()
    }
  }
})()</script></div><script src="/js/live2d.js"></script><canvas class="fireworks" mobile="false"></canvas><script src="https://cdn.jsdelivr.net/npm/butterfly-extsrc@1/dist/fireworks.min.js"></script><script async data-pjax src="//busuanzi.ibruce.info/busuanzi/2.3/busuanzi.pure.mini.js"></script></div></body></html>